package westeel.dataserver.service;

import org.apache.ibatis.builder.SqlSourceBuilder;
import org.aspectj.util.FileUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import westeel.dataserver.config.CacheConfig;
import westeel.dataserver.utils.*;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import static westeel.dataserver.utils.DataSourceFactoryOnStart.APPLICATION_PROPS_XML;

@Service("freeQueryService")
public class FreeQueryServiceImpl implements FreeQueryService {
    private static final char SQL_KEY_PREX = '@';
    private static final String FREE_SQL_ACCESS_MASK = "access_mask";
    public final static String FREE_SQL_TABLE = "free_sql";
    public final static String FREE_SQL_CONTENT = "content";
    public final static String FREE_SQL_KEY = "id";

    //系统数据源不可以删除或改变或查询
    final String SYSTEM_DATA_SOURCE_NAME = "system";

    @Autowired
    DataSourceCreateHelper dataSourceCreateHelper;

    Logger logger = LoggerFactory.getLogger("FreeQueryServiceImpl");

    final static String dataSourceFormat= "%sDataSource";
    static public String genDataSource(String name){
        return String.format(dataSourceFormat,name);
    }
    @PostConstruct
    public void init() throws SQLException, IOException {
        this.makeSureSystemTablesExisted();
    }
    final String FREE_SQL_TABLE_CREATE = String.format("CREATE TABLE IF NOT EXISTS %s (%s varchar(64) primary key ,%s text not null,%s int default 0 )",FREE_SQL_TABLE,FREE_SQL_KEY, FREE_SQL_CONTENT,FREE_SQL_ACCESS_MASK);
    private void makeSureSystemTablesExisted() throws SQLException {
        try {
            Files.createDirectory(Paths.get("db"));
        } catch (IOException e) {

        }
        JdbcTemplate systemDbTemplate = getJdbcTemplate(this.getSystemDataSourceName());
            systemDbTemplate.execute(FREE_SQL_TABLE_CREATE);
    }

    @Override
    public List query(String dataSourceName,String sql) throws SQLException {
        reportErrIfDataSourceIsSystem(dataSourceName);
        JdbcTemplate jdbcTemplate = getJdbcTemplate(dataSourceName);
        try {
            sql = getRealSql(sql);
            return jdbcTemplate.queryForList(sql);
        }catch (Exception e){
            logger.error("exec error:",e);
            throw new SQLException("exec error:"+sql);
        }
    }


    @Override
    public List queryWithParams(String dataSourceName, String sql, Map params)  throws SQLException {
        NamedParameterJdbcTemplate jdbcTemplate = getNamedParameterJdbcTemplate(dataSourceName);
        try {
            sql = getRealSql(sql);
            return jdbcTemplate.queryForList(sql,params);
        }catch (Exception e){
            logger.error("exec error:",e);
            throw new SQLException("exec error:"+sql);
        }
    }

    private static JdbcTemplate getJdbcTemplate(String dataSourceName) throws SQLException {
        String beanName4DataSource = genDataSource(dataSourceName);
        DataSource dataSource = SpringHelper.getBean(beanName4DataSource);
        if(dataSource == null) {
            throw new SQLException("datasource " + dataSourceName + " is not found!");
        }
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }
    private static NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(String dataSourceName) throws SQLException {
        String beanName4DataSource = genDataSource(dataSourceName);
        DataSource dataSource = SpringHelper.getBean(beanName4DataSource);
        if(dataSource == null) {
            throw new SQLException("datasource " + dataSourceName + " is not found!");
        }
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        return jdbcTemplate;
    }

    @Override
    public void createDataSource(String name, Map<String,Object> properties){
        String beanName4DataSource = genDataSource(name);
        dataSourceCreateHelper.createDataSource(DataSourceFactoryOnStart.dataSourceNames,beanName4DataSource,properties);
        Properties appProperties = DataSourceFactoryOnStart.GetApplicationProperties();
        DataSourceUtils.addDataSourceInfo(appProperties,beanName4DataSource, CamelNameUtils.CamelName4Variable(properties));
        //要排除系统数据源
        DataSourceUtils.removeDataSourceInfo(appProperties,SYSTEM_DATA_SOURCE_NAME);
        DataSourceFactoryOnStart.SavePropAsXml(appProperties,APPLICATION_PROPS_XML);
    }

    @Override
    public void removeDataSource(String name) {
        String beanName4DataSource = genDataSource(name);
        dataSourceCreateHelper.removeDataSource(DataSourceFactoryOnStart.dataSourceNames,beanName4DataSource);
        Properties appProperties = DataSourceFactoryOnStart.GetApplicationProperties();
        DataSourceUtils.removeDataSourceInfo(appProperties,beanName4DataSource);
        DataSourceFactoryOnStart.SavePropAsXml(appProperties,APPLICATION_PROPS_XML);
    }

    @Override
    public long update(String dataSourceName, String sql)  throws SQLException {
        JdbcTemplate jdbcTemplate = getJdbcTemplate(dataSourceName);
        sql = getRealSql(sql);
        return jdbcTemplate.update(sql);
    }

    /**
     * 获取真正的sql
     * @param key
     * @return
     */
    @Cacheable(value=CacheConfig.SQL_CACHE,key = "#key")
    public String getRealSqlByKey(String key){
        try {
            JdbcTemplate jdbcTemplate = getJdbcTemplate(SYSTEM_DATA_SOURCE_NAME);
            Map result = jdbcTemplate.queryForMap(String.format("SELECT %s,%s FROM %s WHERE id = \"%s\"",FREE_SQL_KEY, FREE_SQL_CONTENT,FREE_SQL_TABLE,key));
            if(result == null){
                return null;
            }
            return (String) result.get(FREE_SQL_CONTENT);
        }catch (Exception e){
            logger.error("getRealSqlByKey " + key,e);
            return null;
        }
    }
    public String getRealSql(String sqlOrSqlkey) {
        //如果用key访问sql,则格式为:@key 否则为普通sql
        if(sqlOrSqlkey.charAt(0) == SQL_KEY_PREX){
            //sqlkey
            //从数据库中获取真实的sql
            return getRealSqlByKey(sqlOrSqlkey.substring(1));
        }
        return sqlOrSqlkey;
    }
    @Override
    public long updateWithParams(String dataSourceName, String sql, Map params)  throws SQLException {
        sql = getRealSql(sql);
        NamedParameterJdbcTemplate jdbcTemplate = getNamedParameterJdbcTemplate(dataSourceName);
        return jdbcTemplate.update(sql,params);
    }

    @Override
    public void reportErrIfDataSourceIsSystem(String dataSourceName) {
        if (SYSTEM_DATA_SOURCE_NAME.equals(dataSourceName)){
            throw new RuntimeException("system data source is not allowed!");
        }
    }

    @Override
    public String getSystemDataSourceName() {
        return SYSTEM_DATA_SOURCE_NAME;
    }
    @Override
    @CacheEvict(value = CacheConfig.SQL_CACHE,key = "#key")
    public long removeSql(String key) throws SQLException {
        JdbcTemplate jdbcTemplate = getJdbcTemplate(SYSTEM_DATA_SOURCE_NAME);
        return jdbcTemplate.update(String.format("DELETE * FROM %s WHERE %s = \"%s\"",FREE_SQL_TABLE,FREE_SQL_KEY,key));
    }

    @Override
    @CacheEvict(value = CacheConfig.SQL_CACHE,key = "#key")
    public long createSql(String key, String sql, String userGroup) throws SQLException {
        JdbcTemplate jdbcTemplate = getJdbcTemplate(SYSTEM_DATA_SOURCE_NAME);
        return jdbcTemplate.update(String.format("INSERT INTO %s ( %s,%s,%s ) VALUES (\"%s\",%d,\"%s\")",FREE_SQL_TABLE,FREE_SQL_KEY,FREE_SQL_ACCESS_MASK,FREE_SQL_CONTENT,key,0,sql));
    }

    @Override
    @CacheEvict(value = CacheConfig.SQL_CACHE,key = "#key")
    public long updateSql(String key, String sql, String userGroup) throws SQLException {
        JdbcTemplate jdbcTemplate = getJdbcTemplate(SYSTEM_DATA_SOURCE_NAME);
        return jdbcTemplate.update(String.format("UPDATE %s SET %s = \"%s\",%s = %d,%s= \"%s\" )",FREE_SQL_TABLE,FREE_SQL_KEY,key,FREE_SQL_ACCESS_MASK,0,FREE_SQL_CONTENT,sql));
    }
}
